1. Technical Field
The present invention relates in general to a system and method for accessing remote databases. More particularly, the present invention relates to a system and method that virtualizes stored procedures to allow the use of non-native stored procedures from a database environment.
2. Computer Program Listing Appendix
A Computer Program Listing Appendix has been filed concurrently with the present application and is incorporated by reference in its entirety, herein. Listings of four computer programs in the Java programming language are included on the CD-ROM in compliance with CFR §1.96 and MPEP §608.05. Two identical copies of the CD-ROM, labeled “Copy 1” and “Copy 2,” have been included.
The files “INIT”, “CREATEJAVASOURCECODE.TXT”, and “DB2ORACLEDATATYPEMAPPING.TXT,” were created on or before Jul. 21, 2005, and the program named “DB2SPNAME.TXT” was created on or before Jun. 7, 2006.
3. Description of the Related Art
Relational database applications have the ability to run “stored procedures” that are essentially, software applications with SQL statements and other control-flow language instructions. While stored procedures usually include SQL statements, they differ from SQL statements in that stored procedures are generally pre-compiled by the database management system (DBMS). Stored procedures are widely used by relational database application developers. Some of this popularity stems from the pre-compilation aspects of stored procedures, as mentioned above.
As indicated above, stored procedures are used with a particular database management system (DBMS). A DBMS is a collection of programs that enables the user to store, modify, and extract information from a database. There are many different types of DBMSs, ranging from small systems that run on personal computers to huge systems that run on mainframes. There are also many different vendors that supply DBMSs, such as International Business Machines Corporation (IBM), Oracle Corporation, Microsoft Corporation, Teradata, Sybase, Inc. and others. These DBMS vendors often offer multiple DBMS products to serve different needs and markets. Consequently, each DBMS has its strengths and weaknesses when compared with other DBMSs.
Stored procedures in most modern database environments have many similarities to other programming languages. In particular, stored procedures are often able to accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch; and contain programming statements that perform operations in the database, including calling other procedures; return a status value to a calling procedure or batch to indicate success or failure (and the reason for the failure).
While stored procedures are widely used, there are some shortcomings to their use. One shortcoming faced by relational database application developers is that stored procedures are specific to a particular DBMS. In other words, a stored procedure native to a first type of DBMS cannot inherently call stored procedures that are native to a second, different, type of DBMS.
What is needed, therefore, is a system, method, and program product that virtualizes a stored procedure native to a remote DBMS so that it can be integrated with a local remote procedure. What is further needed is a system, method, and program product that runs the remote stored procedure on the remote DBMS and returns the resulting data to the local stored procedure in a format usable by the local stored procedure.